﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Customs.Components.KDT.Khac.Info;
using Microsoft.ApplicationBlocks.Data;

namespace Customs.Components.KDT.Khac
{
    public class ToKhaiMauDich : ToKhaiMauDichInfo
    {
        //---------------------------------------------------------------------------------------------
        public void Load(ToKhaiMauDichInfo entityInfo)
        {            
            this._SoTiepNhan = entityInfo.SoTiepNhan;
            this._NgayTiepNhan = entityInfo.NgayTiepNhan;
            this._MaHaiQuan = entityInfo.MaHaiQuan;
            this._SoToKhai = entityInfo.SoToKhai;
            this._MaLoaiHinh = entityInfo.MaLoaiHinh;
            this._NgayDangKy = entityInfo.NgayDangKy;
            this._MaDoanhNghiep = entityInfo.MaDoanhNghiep;
            this._TenDoanhNghiep = entityInfo.TenDoanhNghiep;
            this._MaDaiLyTTHQ = entityInfo.MaDaiLyTTHQ;
            this._TenDaiLyTTHQ = entityInfo.TenDaiLyTTHQ;
            this._TenDonViDoiTac = entityInfo.TenDonViDoiTac;
            this._ChiTietDonViDoiTac = entityInfo.ChiTietDonViDoiTac;
            this._SoGiayPhep = entityInfo.SoGiayPhep;
            this._NgayGiayPhep = entityInfo.NgayGiayPhep;
            this._NgayHetHanGiayPhep = entityInfo.NgayHetHanGiayPhep;
            this._SoHopDong = entityInfo.SoHopDong;
            this._NgayHopDong = entityInfo.NgayHopDong;
            this._NgayHetHanHopDong = entityInfo.NgayHetHanHopDong;
            this._SoHoaDonThuongMai = entityInfo.SoHoaDonThuongMai;
            this._NgayHoaDonThuongMai = entityInfo.NgayHoaDonThuongMai;
            this._PTVT_ID = entityInfo.PTVT_ID;
            this._SoHieuPTVT = entityInfo.SoHieuPTVT;
            this._NgayDenPTVT = entityInfo.NgayDenPTVT;
            this._QuocTichPTVT_ID = entityInfo.QuocTichPTVT_ID;
            this._LoaiVanDon = entityInfo.LoaiVanDon;
            this._SoVanDon = entityInfo.SoVanDon;
            this._NgayVanDon = entityInfo.NgayVanDon;
            this._NuocXK_ID = entityInfo.NuocXK_ID;
            this._NuocNK_ID = entityInfo.NuocNK_ID;
            this._DiaDiemXepHang = entityInfo.DiaDiemXepHang;
            this._CuaKhau_ID = entityInfo.CuaKhau_ID;
            this._DKGH_ID = entityInfo.DKGH_ID;
            this._NguyenTe_ID = entityInfo.NguyenTe_ID;
            this._TyGiaTinhThue = entityInfo.TyGiaTinhThue;
            this._TyGiaUSD = entityInfo.TyGiaUSD;
            this._PTTT_ID = entityInfo.PTTT_ID;
            this._SoHang = entityInfo.SoHang;
            this._SoLuongPLTK = entityInfo.SoLuongPLTK;
            this._TenChuHang = entityInfo.TenChuHang;
            this._SoContainer20 = entityInfo.SoContainer20;
            this._SoContainer40 = entityInfo.SoContainer40;
            this._SoKien = entityInfo.SoKien;
            this._TrongLuong = entityInfo.TrongLuong;
            this._TongTriGiaKhaiBao = entityInfo.TongTriGiaKhaiBao;
            this._TongTriGiaTinhThue = entityInfo.TongTriGiaTinhThue;
            this._TrangThaiXuLy = entityInfo.TrangThaiXuLy;
            this._LoaiToKhaiGiaCong = entityInfo.LoaiToKhaiGiaCong;
            this._LePhiHaiQuan = entityInfo.LePhiHaiQuan;
            this._PhiBaoHiem = entityInfo.PhiBaoHiem;
            this._PhiVanChuyen = entityInfo.PhiVanChuyen;
            this._PhiXepDoHang = entityInfo.PhiXepDoHang;
            this._PhiKhac = entityInfo.PhiKhac;
            this._CanBoDangKy = entityInfo.CanBoDangKy;
            this._QuanLyMay = entityInfo.QuanLyMay;
            this._LoaiHangHoa = entityInfo.LoaiHangHoa;
            this._HMDInfoCollection = entityInfo.HMDInfoCollection;
            this._ChungTuCollection = entityInfo.ChungTuCollection;
            this.GiayTo = entityInfo.GiayTo;
        }

        //---------------------------------------------------------------------------------------------

        public long Insert(SqlTransaction transaction)
        {
            string sql = "INSERT INTO DTOKHAIMD "+
		"( "+
			"So_TNDKDT, "+
			"Ma_HQTN, "+
			"Nam_TN, "+
			"So_TK, "+
			"Ma_LH, "+
			"Nhom_LH, "+
			"Ma_HQ, "+
			"NamDK, "+
			"Ngay_DK, "+
			"Ma_DV, "+
			"Ten_DV, "+
			"Ma_DVUT, "+
			"Ten_DVUT, "+
			"DaiLy_TTHQ, "+
			"TenDLTTHQ, "+
			"DV_DT, "+
			"So_GP, "+
			"Ngay_GP, "+
			"Ngay_HHGP, "+
			"So_HD, "+
			"Ngay_HD, "+
			"Ngay_HHHD, "+
			"So_HDTM, "+
			"Ngay_HDTM, "+
			"Ma_PTVT, "+
			"Ten_PTVT, "+
			"NgayKH, "+
			"NgayDen, "+
			"Van_Don, "+
			"Ngay_VanDon, "+
			"Nuoc_XK, "+
			"Nuoc_NK, "+
			"CangNN, "+
			"Ma_CK, "+
			"Ma_GH, "+
			"Ma_NT, "+
			"TyGia_VND, "+
			"TyGia_USD, "+
			"Ma_PTTT, "+
			"GiayTo, "+
			"SoHang, "+
			"So_PLTK, "+
			"TenCH, "+
			"Ngay_KB, "+
			"So_Container, "+
			"So_Container40, "+
			"So_Kien, "+
			"Tr_Luong, "+
			"Pass_GC, "+
			"User_DK, "+
			"TongTGKB, "+
			"TongTGTT, "+
			"TrangThaiXL, "+
			"KieuKB, "+
			"Ma_DVKB, "+
			"QLMay, "+
			"LoaiTKGC, "+
			"DDiem_KH, "+
			"Ngay_KH, "+
			"LePhi_HQ, "+
			"Phi_BH, "+
			"Phi_VC, "+
			"XUAT_NPL_SP, "+
			"Ngay_TN	, "+
			"NgayXL "+
		")"+
		"VALUES "+
		"( "+
			"@SoTiepNhanKDT, "+
			"@MaHaiQuan,			"+
			"Year(@NgayTiepNhan),															"+
			"null,																	"+
			"@MaLoaiHinh,	"+
			"LEFT(@MaLoaiHinh, 3),	"+
			"@MaHaiQuan, "+
            "Year(@NgayTiepNhan),	 " +
			"@NgayTiepNhan,	"+
			"CAST(@MaDoanhNghiep AS char(14)),"+
			"CAST(@TenDoanhNghiep AS NVARCHAR(50)),	"+
			"'',										"+
			"'',					"+
			"CAST(@MaDaiLyTTHQ AS char(14)),			"+
			"CAST(@TenDaiLyTTHQ AS NVARCHAR(50)),	"+
			"@TenDonViDoiTac,		"+
			"@SoGiayPhep,			"+

			"CASE WHEN @NgayGiayPhep = '01/01/1900' THEN null ELSE @NgayGiayPhep END, "+																					
			"CASE WHEN @NgayHetHanGiayPhep = '01/01/1900' THEN null ELSE @NgayHetHanGiayPhep END, "+
			"@SoHopDong,		"+								
			"CASE WHEN @NgayHopDong = '01/01/1900' THEN null ELSE @NgayHopDong END, "+																					
			"CASE WHEN @NgayHetHanHopDong = '01/01/1900' THEN null ELSE @NgayHetHanHopDong END, "+
			"@SoHoaDonThuongMai,		"+
			"CASE WHEN @NgayHoaDonThuongMai = '01/01/1900' THEN null ELSE @NgayHoaDonThuongMai END, "+
			"@PTVT_ID,	"+
			"@SoHieuPTVT, "+
			"null,	"+
			"@NgayDenPTVT,		"+
			"@SoVanDon, "+
			"CASE WHEN @NgayVanDon = '01/01/1900' THEN null ELSE @NgayVanDon END, "+
			"@NuocXK_ID,			"+
			"@NuocNK_ID,			"+
			"@DiaDiemXepHang,		"+
			"@CuaKhau_ID,			"+
			"@DKGH_ID,					"+
			"@NguyenTe_ID,				"+
			"@TyGiaTinhThue,		"+
			"@TyGiaUSD,			"+
			"@PTTT_ID,				"+
			"@GiayTo,			"+
			"@SoHang,			"+
			"@SoLuongPLTK,					"+
			"@TenChuHang,					"+
			"@NgayTiepNhan,					"+																					
			"CASE WHEN @SoContainer20 = 0 THEN null ELSE @SoContainer20 END, "+																			
			"CASE WHEN @SoContainer40 = 0 THEN null ELSE @SoContainer40 END, "+																					
			"CASE WHEN @SoKien = 0 THEN null ELSE @SoKien END, "+
			
			"@TrongLuong,	"+
			"0,	"+
			"@CanBoDangKy,	"+
			"@TongTriGiaKhaiBao,		"+
			"@TongTriGiaTinhThue, "+
			"0,					"+
			"0,					"+
			"null,				"+
            "@QuanLyMay,		" +
			"'KHA',				"+
			"null,				"+
			"null,				"+
			"@LePhiHaiQuan,	 "+
			"@PhiBaoHiem,			"+
			"@PhiVanChuyen, "+
			"@XUAT_NPL_SP, "+
			"@NgayTiepNhan, "+
			"@NgayXL "+			
		")";
            SqlParameter[] parameters = new SqlParameter[57];

            parameters[0] = new SqlParameter("@SoTiepNhanKDT", SqlDbType.BigInt);
            parameters[0].Direction = ParameterDirection.Input;
            parameters[0].Value = this._SoTiepNhan;

            parameters[1] = new SqlParameter("@NgayTiepNhan", SqlDbType.DateTime);
            parameters[1].Direction = ParameterDirection.Input;
            parameters[1].Value = this._NgayTiepNhan;

            parameters[2] = new SqlParameter("@MaHaiQuan", SqlDbType.Char);
            parameters[2].Size = 6;
            parameters[2].Direction = ParameterDirection.Input;
            parameters[2].Value = this._MaHaiQuan;

            parameters[3] = new SqlParameter("@SoToKhai", SqlDbType.Int);
            parameters[3].Direction = ParameterDirection.Input;
            parameters[3].Value = this._SoToKhai;

            parameters[4] = new SqlParameter("@MaLoaiHinh", SqlDbType.Char);
            parameters[4].Size = 5;
            parameters[4].Direction = ParameterDirection.Input;
            parameters[4].Value = this._MaLoaiHinh;

            parameters[5] = new SqlParameter("@NgayDangKy", SqlDbType.DateTime);
            parameters[5].Direction = ParameterDirection.Input;
            parameters[5].Value = this._NgayDangKy;

            parameters[6] = new SqlParameter("@MaDoanhNghiep", SqlDbType.VarChar);
            parameters[6].Size = 14;
            parameters[6].Direction = ParameterDirection.Input;
            parameters[6].Value = this._MaDoanhNghiep;

            parameters[7] = new SqlParameter("@TenDoanhNghiep", SqlDbType.NVarChar);
            parameters[7].Size = 255;
            parameters[7].Direction = ParameterDirection.Input;
            parameters[7].Value = this._TenDoanhNghiep;

            parameters[8] = new SqlParameter("@MaDaiLyTTHQ", SqlDbType.VarChar);
            parameters[8].Size = 14;
            parameters[8].Direction = ParameterDirection.Input;
            parameters[8].Value = this._MaDaiLyTTHQ;

            parameters[9] = new SqlParameter("@TenDaiLyTTHQ", SqlDbType.NVarChar);
            parameters[9].Size = 50;
            parameters[9].Direction = ParameterDirection.Input;
            parameters[9].Value = this._TenDaiLyTTHQ;

            parameters[10] = new SqlParameter("@TenDonViDoiTac", SqlDbType.NVarChar);
            parameters[10].Size = 40;
            parameters[10].Direction = ParameterDirection.Input;
            parameters[10].Value = this._TenDonViDoiTac;

            parameters[11] = new SqlParameter("@ChiTietDonViDoiTac", SqlDbType.NVarChar);
            parameters[11].Size = 40;
            parameters[11].Direction = ParameterDirection.Input;
            parameters[11].Value = this._ChiTietDonViDoiTac;

            parameters[12] = new SqlParameter("@SoGiayPhep", SqlDbType.NVarChar);
            parameters[12].Size = 50;
            parameters[12].Direction = ParameterDirection.Input;
            parameters[12].Value = this._SoGiayPhep;

            parameters[13] = new SqlParameter("@NgayGiayPhep", SqlDbType.DateTime);
            parameters[13].Direction = ParameterDirection.Input;
            parameters[13].Value = this._NgayGiayPhep;

            parameters[14] = new SqlParameter("@NgayHetHanGiayPhep", SqlDbType.DateTime);
            parameters[14].Direction = ParameterDirection.Input;
            parameters[14].Value = this._NgayHetHanGiayPhep;

            parameters[15] = new SqlParameter("@SoHopDong", SqlDbType.NVarChar);
            parameters[15].Size = 50;
            parameters[15].Direction = ParameterDirection.Input;
            parameters[15].Value = this._SoHopDong;

            parameters[16] = new SqlParameter("@NgayHopDong", SqlDbType.DateTime);
            parameters[16].Direction = ParameterDirection.Input;
            parameters[16].Value = this._NgayHopDong;

            parameters[17] = new SqlParameter("@NgayHetHanHopDong", SqlDbType.DateTime);
            parameters[17].Direction = ParameterDirection.Input;
            parameters[17].Value = this._NgayHetHanHopDong;

            parameters[18] = new SqlParameter("@SoHoaDonThuongMai", SqlDbType.NVarChar);
            parameters[18].Size = 50;
            parameters[18].Direction = ParameterDirection.Input;
            parameters[18].Value = this._SoHoaDonThuongMai;

            parameters[19] = new SqlParameter("@NgayHoaDonThuongMai", SqlDbType.DateTime);
            parameters[19].Direction = ParameterDirection.Input;
            parameters[19].Value = this._NgayHoaDonThuongMai;

            parameters[20] = new SqlParameter("@PTVT_ID", SqlDbType.VarChar);
            parameters[20].Size = 3;
            parameters[20].Direction = ParameterDirection.Input;
            parameters[20].Value = this._PTVT_ID;

            parameters[21] = new SqlParameter("@SoHieuPTVT", SqlDbType.NVarChar);
            parameters[21].Size = 50;
            parameters[21].Direction = ParameterDirection.Input;
            parameters[21].Value = this._SoHieuPTVT;

            parameters[22] = new SqlParameter("@NgayDenPTVT", SqlDbType.DateTime);
            parameters[22].Direction = ParameterDirection.Input;
            parameters[22].Value = this._NgayDenPTVT;

            parameters[23] = new SqlParameter("@QuocTichPTVT_ID", SqlDbType.Char);
            parameters[23].Size = 3;
            parameters[23].Direction = ParameterDirection.Input;
            parameters[23].Value = this._QuocTichPTVT_ID;

            parameters[24] = new SqlParameter("@LoaiVanDon", SqlDbType.NVarChar);
            parameters[24].Size = 50;
            parameters[24].Direction = ParameterDirection.Input;
            parameters[24].Value = this._LoaiVanDon;

            parameters[25] = new SqlParameter("@SoVanDon", SqlDbType.NVarChar);
            parameters[25].Size = 50;
            parameters[25].Direction = ParameterDirection.Input;
            parameters[25].Value = this._SoVanDon;

            parameters[26] = new SqlParameter("@NgayVanDon", SqlDbType.DateTime);
            parameters[26].Direction = ParameterDirection.Input;
            parameters[26].Value = this._NgayVanDon;

            parameters[27] = new SqlParameter("@NuocXK_ID", SqlDbType.Char);
            parameters[27].Size = 3;
            parameters[27].Direction = ParameterDirection.Input;
            parameters[27].Value = this._NuocXK_ID;

            parameters[28] = new SqlParameter("@NuocNK_ID", SqlDbType.Char);
            parameters[28].Size = 3;
            parameters[28].Direction = ParameterDirection.Input;
            parameters[28].Value = this._NuocNK_ID;

            parameters[29] = new SqlParameter("@DiaDiemXepHang", SqlDbType.NVarChar);
            parameters[29].Size = 255;
            parameters[29].Direction = ParameterDirection.Input;
            parameters[29].Value = this._DiaDiemXepHang;

            parameters[30] = new SqlParameter("@CuaKhau_ID", SqlDbType.Char);
            parameters[30].Size = 4;
            parameters[30].Direction = ParameterDirection.Input;
            parameters[30].Value = this._CuaKhau_ID;

            parameters[31] = new SqlParameter("@DKGH_ID", SqlDbType.VarChar);
            parameters[31].Size = 7;
            parameters[31].Direction = ParameterDirection.Input;
            parameters[31].Value = this._DKGH_ID;

            parameters[32] = new SqlParameter("@NguyenTe_ID", SqlDbType.Char);
            parameters[32].Size = 3;
            parameters[32].Direction = ParameterDirection.Input;
            parameters[32].Value = this._NguyenTe_ID;

            parameters[33] = new SqlParameter("@TyGiaTinhThue", SqlDbType.Money);
            parameters[33].Precision = 19;
            parameters[33].Scale = 4;
            parameters[33].Direction = ParameterDirection.Input;
            parameters[33].Value = this._TyGiaTinhThue;

            parameters[34] = new SqlParameter("@TyGiaUSD", SqlDbType.Money);
            parameters[34].Precision = 19;
            parameters[34].Scale = 4;
            parameters[34].Direction = ParameterDirection.Input;
            parameters[34].Value = this._TyGiaUSD;

            parameters[35] = new SqlParameter("@PTTT_ID", SqlDbType.VarChar);
            parameters[35].Size = 10;
            parameters[35].Direction = ParameterDirection.Input;
            parameters[35].Value = this._PTTT_ID;

            parameters[36] = new SqlParameter("@SoHang", SqlDbType.SmallInt);
            parameters[36].Direction = ParameterDirection.Input;
            parameters[36].Value = this._SoHang;

            parameters[37] = new SqlParameter("@SoLuongPLTK", SqlDbType.SmallInt);
            parameters[37].Direction = ParameterDirection.Input;
            parameters[37].Value = this._SoLuongPLTK;

            parameters[38] = new SqlParameter("@TenChuHang", SqlDbType.NVarChar);
            parameters[38].Size = 30;
            parameters[38].Direction = ParameterDirection.Input;
            parameters[38].Value = this._TenChuHang;

            parameters[39] = new SqlParameter("@SoContainer20", SqlDbType.Decimal);
            parameters[39].Precision = 15;
            parameters[39].Scale = 0;
            parameters[39].Direction = ParameterDirection.Input;
            parameters[39].Value = this._SoContainer20;

            parameters[40] = new SqlParameter("@SoContainer40", SqlDbType.Decimal);
            parameters[40].Precision = 15;
            parameters[40].Scale = 0;
            parameters[40].Direction = ParameterDirection.Input;
            parameters[40].Value = this._SoContainer40;

            parameters[41] = new SqlParameter("@SoKien", SqlDbType.Decimal);
            parameters[41].Precision = 15;
            parameters[41].Scale = 0;
            parameters[41].Direction = ParameterDirection.Input;
            parameters[41].Value = this._SoKien;

            parameters[42] = new SqlParameter("@TrongLuong", SqlDbType.Decimal);
            parameters[42].Precision = 18;
            parameters[42].Scale = 2;
            parameters[42].Direction = ParameterDirection.Input;
            parameters[42].Value = this._TrongLuong;

            parameters[43] = new SqlParameter("@TongTriGiaKhaiBao", SqlDbType.Money);
            parameters[43].Precision = 19;
            parameters[43].Scale = 4;
            parameters[43].Direction = ParameterDirection.Input;
            parameters[43].Value = this._TongTriGiaKhaiBao;

            parameters[44] = new SqlParameter("@TongTriGiaTinhThue", SqlDbType.Money);
            parameters[44].Precision = 19;
            parameters[44].Scale = 4;
            parameters[44].Direction = ParameterDirection.Input;
            parameters[44].Value = this._TongTriGiaTinhThue;

            parameters[45] = new SqlParameter("@TrangThaiXuLy", SqlDbType.Int);
            parameters[45].Direction = ParameterDirection.Input;
            parameters[45].Value = this._TrangThaiXuLy;

            parameters[46] = new SqlParameter("@LoaiToKhaiGiaCong", SqlDbType.VarChar);
            parameters[46].Size = 3;
            parameters[46].Direction = ParameterDirection.Input;
            parameters[46].Value = this._LoaiToKhaiGiaCong;

            parameters[47] = new SqlParameter("@LePhiHaiQuan", SqlDbType.Money);
            parameters[47].Precision = 19;
            parameters[47].Scale = 4;
            parameters[47].Direction = ParameterDirection.Input;
            parameters[47].Value = this._LePhiHaiQuan;

            parameters[48] = new SqlParameter("@PhiBaoHiem", SqlDbType.Money);
            parameters[48].Precision = 19;
            parameters[48].Scale = 4;
            parameters[48].Direction = ParameterDirection.Input;
            parameters[48].Value = this._PhiBaoHiem;

            parameters[49] = new SqlParameter("@PhiVanChuyen", SqlDbType.Money);
            parameters[49].Precision = 19;
            parameters[49].Scale = 4;
            parameters[49].Direction = ParameterDirection.Input;
            parameters[49].Value = this._PhiVanChuyen;

            parameters[50] = new SqlParameter("@PhiXepDoHang", SqlDbType.Money);
            parameters[50].Precision = 19;
            parameters[50].Scale = 4;
            parameters[50].Direction = ParameterDirection.Input;
            parameters[50].Value = this._PhiXepDoHang;

            parameters[51] = new SqlParameter("@PhiKhac", SqlDbType.Money);
            parameters[51].Precision = 19;
            parameters[51].Scale = 4;
            parameters[51].Direction = ParameterDirection.Input;
            parameters[51].Value = this._PhiKhac;

            parameters[52] = new SqlParameter("@CanBoDangKy", SqlDbType.VarChar);
            parameters[52].Size = 100;
            parameters[52].Direction = ParameterDirection.Input;
            parameters[52].Value = this._CanBoDangKy;

            parameters[53] = new SqlParameter("@QuanLyMay", SqlDbType.Bit);
            parameters[53].Direction = ParameterDirection.Input;
            parameters[53].Value = this._QuanLyMay;

            parameters[54] = new SqlParameter("@XUAT_NPL_SP", SqlDbType.Char);
            parameters[54].Direction = ParameterDirection.Input;
            parameters[54].Value = this.LoaiHangHoa;

            parameters[55] = new SqlParameter("@GiayTo", SqlDbType.NVarChar);
            parameters[55].Size = 40;
            parameters[55].Direction = ParameterDirection.Input;
            parameters[55].Value = this.GiayTo;


            parameters[56] = new SqlParameter("@NgayXL", SqlDbType.DateTime);            
            parameters[56].Direction = ParameterDirection.Input;
            parameters[56].Value = DateTime.Today;
            SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sql, parameters);
            return (long) parameters[0].Value;
        }

        //-----------------------------------------------------------------------------------------
        /// <summary>
        /// Đăng ký thông tin nguyên phụ liệu.
        /// </summary>
        /// <returns>Số tiếp nhận</returns>
        public long InsertFull()
        {
            long sotiepnhan;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString))
            {
                conn.Open();
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    string msg = string.Empty; 
                    try
                    {
                        bool isOK = true;
                        if (this.SoTiepNhan>0)
                        {
                            HuyThongTinKhaiBao(this.SoTiepNhan, this.MaHaiQuan, this.MaDoanhNghiep, this.NgayTiepNhan.Year,transaction);
                        }
                        this.NgayTiepNhan = DateTime.Today;
                        this.SoTiepNhan = GetSoTiepNhan(transaction);
                        this.Insert(transaction);
                        if (this.SoTiepNhan > 0)
                        {                            
                            foreach (HangMauDichInfo h in this._HMDInfoCollection)
                            {
                                h.SoTiepNhan = this.SoTiepNhan;
                                h.MaHaiQuan = this._MaHaiQuan;
                                h.NamTiepNhan = this._NgayTiepNhan.Year;
                                
                                HangMauDich hmd = new HangMauDich();
                                hmd.Load(h);
                                int ret = hmd.Insert(transaction);
                                if (ret <= 0)
                                {
                                    isOK = false;
                                    break;
                                }
                            }
                            foreach (ChungTuInfo ct in this.ChungTuCollection)
                            {
                                ct.So_TNDKDT = this.SoTiepNhan;
                                ct.Ma_HQTN = this._MaHaiQuan;
                                ct.Nam_TN = (short)this._NgayTiepNhan.Year;
                                ChungTu ctClass = new ChungTu();
                                ctClass.Load(ct);
                                int ret = ctClass.Insert(transaction);
                                if (ret <= 0)
                                {
                                    isOK = false;
                                    break;
                                }
                            }   
                        }
                        else
                        {
                            isOK = false;
                        }    
               
                        if (isOK)
                        {
                            transaction.Commit();
                        }
                        else
                        {
                            transaction.Rollback();                            
                        }
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw new Exception(msg + ": " + ex.Message);
                    }
                }
            }
            return this.SoTiepNhan;
        }
        private static int LoadTrangThai(long soTiepNhan, string maHaiQuan, int namTiepNhan)
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString);
            string sql =String.Format("select TrangThaiXL from DTOKHAIMD where So_TNDKDT={0} and Ma_HQTN='{1}' and Nam_TN={2}",soTiepNhan,maHaiQuan,namTiepNhan);
            return Convert.ToInt32(SqlHelper.ExecuteScalar(conn,CommandType.Text,sql));
        }
        public static int HuyThongTinKhaiBao(long soTiepNhan, string maHaiQuan, string maDoanhNghiep, int namTiepNhan,SqlTransaction transaction)
        {
            // Cascade delete HangMauDich.
            int trangthai = ToKhaiMauDich.LoadTrangThai(soTiepNhan, maHaiQuan, namTiepNhan);
            if (trangthai == 1)
                throw new Exception("123DaDuyet321");
            ChungTu.DeleteByForeignKey(soTiepNhan,maHaiQuan,namTiepNhan,transaction);
            string sql = "DELETE FROM [DTOKHAIMD] "+
	                     "WHERE "+
		                 "[So_TNDKDT] = @SoTiepNhan AND "+
		                 "[Ma_HQTN] = @MaHaiQuan AND "+
		                  "[Nam_TN] = @NamTiepNhan AND "+
		                  "[Ma_DV] = CAST(@MaDoanhNghiep AS char(14))";
                                SqlParameter[] parameters = new SqlParameter[4];

            parameters[0] = new SqlParameter("@SoTiepNhan", SqlDbType.BigInt);
            parameters[0].Direction = ParameterDirection.Input;
            parameters[0].Value = soTiepNhan;

            parameters[1] = new SqlParameter("@MaHaiQuan", SqlDbType.Char);
            parameters[1].Size = 6;
            parameters[1].Direction = ParameterDirection.Input;
            parameters[1].Value = maHaiQuan;
            
            parameters[2] = new SqlParameter("@NamTiepNhan", SqlDbType.SmallInt);
            parameters[2].Direction = ParameterDirection.Input;
            parameters[2].Value = namTiepNhan;

            parameters[3] = new SqlParameter("@MaDoanhNghiep", SqlDbType.VarChar, 14);
            parameters[3].Direction = ParameterDirection.Input;
            parameters[3].Value = maDoanhNghiep;

            return SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sql, parameters);
        }
        public static int HuyThongTinKhaiBao(long soTiepNhan, string maHaiQuan, string maDoanhNghiep, int namTiepNhan)
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString))
            {
                conn.Open();
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    try
                    {
                        HuyThongTinKhaiBao(soTiepNhan, maHaiQuan, maDoanhNghiep, namTiepNhan, transaction);
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw new Exception(ex.Message);
                    }
                }
            }
            return 1;
        }
        private long getSoMax(SqlTransaction transaction)
        {
            string sqlSelect = "SELECT SoMax FROM [DCAPSO_TOKHAI] WHERE ([Ma_HQTN] = @MaHaiQuan) AND ([Nam_TN] = @NamTiepNhan)";

            SqlParameter[] parameters = new SqlParameter[2];

            parameters[0] = new SqlParameter("@MaHaiQuan", SqlDbType.Char, 6);
            parameters[0].Direction = ParameterDirection.Input;
            parameters[0].Value = this._MaHaiQuan;

            parameters[1] = new SqlParameter("@NamTiepNhan", SqlDbType.SmallInt);
            parameters[1].Direction = ParameterDirection.Input;
            parameters[1].Value = (short)this.NgayTiepNhan.Year;

            SqlDataReader reader = SqlHelper.ExecuteReader(transaction, CommandType.Text, sqlSelect, parameters);
            long result = 0;
            if (reader.Read())
            {
                result = Int64.Parse(reader["SoMax"].ToString());
            }
            reader.Close();
            return result;
        }

        private int updateSoMax(SqlTransaction transaction)
        {
            string sqlUpdate = "UPDATE [DCAPSO_TOKHAI] SET [SoMax] = [SoMax] + 1 WHERE ([Ma_HQTN] = @MaHaiQuan) AND ([Nam_TN] = @NamTiepNhan)";

            SqlParameter[] parameters = new SqlParameter[2];

            parameters[0] = new SqlParameter("@MaHaiQuan", SqlDbType.Char, 6);
            parameters[0].Direction = ParameterDirection.Input;
            parameters[0].Value = this._MaHaiQuan;

            parameters[1] = new SqlParameter("@NamTiepNhan", SqlDbType.SmallInt);
            parameters[1].Direction = ParameterDirection.Input;
            parameters[1].Value = (short)this.NgayTiepNhan.Year;

            return SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sqlUpdate, parameters);
        }

        private int insertSoMax(SqlTransaction transaction)
        {
            string sqlInsert = "INSERT INTO [DCAPSO_TOKHAI] ([Ma_HQTN],	[Nam_TN], [SoMax]) VALUES (@MaHaiQuan, @NamTiepNhan, 1)";

            SqlParameter[] parameters = new SqlParameter[2];

            parameters[0] = new SqlParameter("@MaHaiQuan", SqlDbType.Char, 6);
            parameters[0].Direction = ParameterDirection.Input;
            parameters[0].Value = this._MaHaiQuan;

            parameters[1] = new SqlParameter("@NamTiepNhan", SqlDbType.SmallInt);
            parameters[1].Direction = ParameterDirection.Input;
            parameters[1].Value = (short)this.NgayTiepNhan.Year;

            return SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sqlInsert, parameters);
        }

        //-----------------------------------------------------------------------------------------
        public long GetSoTiepNhan(SqlTransaction transaction)
        {
            long somax = this.getSoMax(transaction);
            if (somax == 0)
            {
                this.insertSoMax(transaction);
                somax = 1;
            }
            else
            {
                this.updateSoMax(transaction);
                somax = somax + 1;
            }
            return somax;
        }
    }
}